package server;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Serializable;
import java.rmi.AlreadyBoundException;
import java.rmi.RemoteException;
import java.rmi.registry.LocateRegistry;
import java.rmi.registry.Registry;
import java.rmi.server.UnicastRemoteObject;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.Connection;
import com.sun.rowset.CachedRowSetImpl;

/**
 * Class for handling database connection and methods
 * 
 * @author Anton
 *
 */
public class DatabaseHandlerImpl extends UnicastRemoteObject implements client.DatabaseHandler {
	
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	//See your driver documentation for the proper format of this string :
    private static final String DB_CONN_STRING = "jdbc:mysql://mazari.myvnc.com/moddb";
    //private static final String DB_CONN_STRING = "jdbc:mysql://localhost/moddb";
    //Provided by your driver documentation. In this case, a MySql driver is used : 
    private static final String DRIVER_CLASS_NAME = "org.gjt.mm.mysql.Driver";
    private static final String USER_NAME = "kth";
    private static final String PASSWORD = "kth";

	private Connection connection;
	
	/**
	 * Constructor for DatabaseHandler that opens a database connection
	 * @throws RemoteException 
	 * 
	 * @throws RemoteException
	 */
	public DatabaseHandlerImpl() throws RemoteException{
		super();
		System.out.println("Server constructor");
	    try {
	    	System.out.println("sdfsf");
	       Class.forName(DRIVER_CLASS_NAME).newInstance();
	    }
	    catch (Exception ex){
	       ex.printStackTrace();
	    }

	    try {
	      connection = (Connection) DriverManager.getConnection(DB_CONN_STRING, USER_NAME, PASSWORD);
	    }
	    catch (SQLException e){
	       e.printStackTrace();
	    }
	}

	@Override
	public void addBLOB(String table, String name, byte[] in) throws RemoteException{
		try {
			PreparedStatement s = connection.prepareStatement("insert into "+table+"("+name+") values(null, ?)");
			s.setBinaryStream(1, new ByteArrayInputStream(in), in.length);
			
			s.executeUpdate();
		} catch (SQLException e) {
			// TODO Handle Exception correct
			e.printStackTrace();
		}
	}

	@Override
	public void addData(String tableName, HashMap<String, Serializable> values)throws RemoteException {
		StringBuilder columns = new StringBuilder();
		StringBuilder data = new StringBuilder();
		for(String name:values.keySet()){
			columns.append(name + ",");
			data.append("'" + values.get(name) + "',");
		}
		
		columns = columns.deleteCharAt(columns.length()-1);
		data = data.deleteCharAt(data.length()-1);
		
		try{
			
			PreparedStatement prep = connection.prepareStatement("insert into "+tableName+ "("+columns +") values(" + data + ")");
			System.out.println(prep.toString());
			prep.executeUpdate();
			
		}
		catch(SQLException e ){
			// TODO Handle Exception correct
			e.printStackTrace();
		}
	}

	@Override
	public void createColumn(String tableName, String columnName, String dbType)throws RemoteException {
		System.out.println("getData("+tableName+", "+columnName+", "+dbType+")");
		
		try{
			PreparedStatement prep = connection.prepareStatement("alter table " + tableName + " add " + columnName + " " + dbType);
			prep.executeUpdate();
		} catch (SQLException e) {
			// TODO Handle Exception correct
			e.printStackTrace();
		}
	}

	@Override
	public void createTable(String name)throws RemoteException {
		System.out.println("createTable()");
		System.out.println(name);
		
		try {
			PreparedStatement prep = connection.prepareStatement("create table " + name + " (id int NOT NULL AUTO_INCREMENT, primary key(id))");
			prep.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Handle Exception correct
			e.printStackTrace();
		}
	}

	@Override
	public ArrayList<String> getData(String tableName, String columnReq, String query) throws RemoteException {
		System.out.println("getData("+tableName+", "+columnReq+", "+query+")");
		int ccount;
		try {
			CachedRowSetImpl crs = new CachedRowSetImpl();
			ResultSet rs;
			String sqlquery = "select * from " + tableName;
			if(columnReq != null && query != null){
				sqlquery+= " where " + columnReq + " = '"+query+"'";		
				System.out.println(sqlquery);
			}
			
			PreparedStatement prep = connection.prepareStatement(sqlquery);
			/*if(columnReq != null && query != null){
				try{
					int i = Integer.parseInt(query);					
					prep.setInt(1, i);
				}catch(Exception e){
					prep.setString(1, query);
				}
			}*/
			
			rs = prep.executeQuery(sqlquery);
			ccount = rs.getMetaData().getColumnCount();
			ArrayList<String> alist = new ArrayList<String>();
			while(rs.next()){
				String str = "";
				for(int i = 1;i <= ccount; i++){
					str = str + rs.getObject(i) + " ";
				}
				System.out.println(str);
				alist.add(str);	
			}
			return alist;
		} catch (SQLException e) {
			// TODO Handle Exception correct
			e.printStackTrace();
		}
		
		return null;
	}

	public void deleteData(String str1, String str2, String str3) throws RemoteException {
		try {
			PreparedStatement prep = connection.prepareStatement("Delete from "+str1+" where "+str2+" = ?");
			prep.setString(1, str3);
			prep.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Handle Exception correct
			e.printStackTrace();
		}
	}
	
	/**
	 * Main method for starting the server.
	 * 
	 * @param args
	 */
	public static void main(String args[]){
		
		try {
			System.out.println("Starting Server.main");
			DatabaseHandlerImpl dbImpl = new DatabaseHandlerImpl();
			
			System.out.println("create registry");
			Registry registry = LocateRegistry.createRegistry(1227);

		    registry.bind("dbImpl", dbImpl);
		    
		    System.out.println("Server ready");
		    
		} catch (RemoteException e) {
			System.err.println("Remote exception when starting up server");
			e.printStackTrace();
		} catch (AlreadyBoundException e) {
			System.err.println("Already bound in RMI registry");
			e.printStackTrace();
		}
		
	}
}
